﻿using System;
using System.ComponentModel;
using System.Data.SqlClient;
using QLHS.DTO;

namespace QLHS.DataLayer
{
    public class GiaoVienDAO
    {
        public BindingList<GiaoVienDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select* from GIAOVIEN";
            BindingList<GiaoVienDTO> GiaoVienTable = new BindingList<GiaoVienDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSGV = Convert.ToInt32(reader["MaSoGV"].ToString());
                        string _TenGV = reader["TenGV"].ToString();
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        GiaoVienDTO temp = new GiaoVienDTO
                        {
                            MaSoGV = _MSGV,
                            TenGV = _TenGV,
                            MaSoMH = _MSMH
                        };
                        GiaoVienTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return GiaoVienTable;
        }

        public void Delete(GiaoVienDTO del)
        {
            string sql = string.Format("delete from GIAOVIEN where MaSoGV = {0}", del.MaSoGV);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }           
        }

        public void Insert(GiaoVienDTO InsertObject)
        {
            string sql;
            if (InsertObject.MaSoMH.HasValue)
            {
                sql = string.Format("insert into GIAOVIEN values (N'{0}', {1})", InsertObject.TenGV, InsertObject.MaSoMH);
            }
            else
            {
                sql = string.Format("insert into GIAOVIEN values (N'{0}', NULL)", InsertObject.TenGV);
            }
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(GiaoVienDTO UpdateObject)
        {
            string sql;
            if (UpdateObject.MaSoMH.HasValue)
            {
                sql = string.Format("update GIAOVIEN set TenGV = N'{1}', MasoMH = {2} where MaSoGV = {0}", UpdateObject.MaSoGV, UpdateObject.TenGV, UpdateObject.MaSoMH);
            }
            else
            {
                sql = string.Format("update GIAOVIEN  set TenGV = N'{1}' where MaSoGV = {0}", UpdateObject.MaSoGV, UpdateObject.TenGV);
            }
                SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
             finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public static void ChangeKeyValue(string Old_MSGV, string New_MSGV)
        //{
        //    string sql = string.Format("update GIAOVIEN set MSGV = '{0}' where (MSGV = '{1}')", New_MSGV, Old_MSGV);

        //    SqlConnection Cn = new SqlConnection(Helper.ConnectionString);
        //    SqlCommand Cmd = new SqlCommand(sql, Cn);
        //    Cn.Open();
        //    Cmd.ExecuteNonQuery();
        //    Cn.Close();
        //}

        public BindingList<GiaoVienDTO> GetGiaoVienDataByPrimaryKey(int MSGV)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select * from GIAOVIEN where MaSoGV = {0}", MSGV);
            BindingList<GiaoVienDTO> Result = new BindingList<GiaoVienDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSGV = Convert.ToInt32(reader["MaSoGV"].ToString());
                        string _TenGV = reader["TenGV"].ToString();
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        GiaoVienDTO temp = new GiaoVienDTO
                        {
                            MaSoGV = _MSGV,
                            TenGV = _TenGV,
                            MaSoMH = _MSMH
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return Result;
        }
    }
}
